package com.lin.lostandfound.provider;

import com.lin.lostandfound.utils.StringUtil;

public class SQLSelectProvider {

    public String next(Integer id, boolean next){

        // 选择下一行（上一贴）
        String sql = "SELECT * FROM LostThings lost WHERE lost.status !=2 and lost.id > "+id+" OR lost.id=(SELECT MAX(lost.id) FROM lost) ORDER BY lost.id ASC";
        // 选择上一行（下一贴）
        if(next) {
            sql = "SELECT * FROM LostThings lost WHERE lost.status !=2 and lost.id < "+id+" OR lost.id=(SELECT MIN(lost.id) FROM lost) ORDER BY lost.id DESC";
        }

        return sql;
    }
    public String previous(Integer id, boolean next){

        // 选择下一行（上一贴）
        String sql = "SELECT * FROM PickThings pick WHERE pick.status !=2 and pick.id > "+id+" OR pick.id=(SELECT MAX(pick.id) FROM pick) ORDER BY pick.id ASC";
        // 选择上一行（下一贴）
        if(next) {
            sql = "SELECT * FROM PickThings pick WHERE pick.status !=2 and pick.id < "+id+" OR pick.id=(SELECT MIN(pick.id) FROM pick) ORDER BY pick.id DESC";
        }

        return sql;
    }


    public String getusercount(String key){

        String sql = "SELECT COUNT(1) FROM User u WHERE u.state != 3 ";
        StringUtil util = new StringUtil();
        if (util.notnull(key)) {
            sql += "AND (u.userName LIKE '%" + key + "%' OR u.grade LIKE '%" + key + "%' OR u.name LIKE '%" + key + "%')";
        }


        return sql;
    }
}
